*******************************************************************************
*set working directory
global folder ".."


cd $folder\temp


*******************************************************************************
* Flow of Funds
*******************************************************************************
* Flow
insheet using "$folder\data\FRB_Z1_F210_20201025.csv",  clear 
g tq = quarterly(series, "YQ")
format tq %tq
g treasury_mutual_fund = mutualfundstreasurysecuritiesass
g treasury_bank = uschartereddepositoryinstitution
g treasury_broker_dealer = securitybrokersanddealerstreasur
g treasury_etf = exchangetradedfundstreasurysecur
g treasury_close_end_fund = closedendfundstreasurysecurities
g treasury_insurance = propertycasualtyinsurancecompani+lifeinsurancecompaniestreasuryse
g treasury_mmf = moneymarketfundstreasurysecuriti
g treasury_household = householdsandnonprofitorganizati
g treasury_gov = - federalgovernmenttreasurysecurit // add negative sign for treasury
g treasury_fed = monetaryauthoritytotaltreasuryse //monetaryauthoritytreasurysecurit
g treasury_row =	restoftheworldtreasurysecurities
g treasury_pension_fund =	privatepensionfundstreasurysecur
g treasury_local_gov =	stateandlocalgovernmentstreasury
g treasury_foreign_bank = foreignbankingofficesintheustrea
g treasury_abs = issuersofassetbackedsecuritiestr
g treasury_credit_union = creditunionstreasurysecuritiesas 
g treasury_gse = governmentsponsoredenterprisestr 
g treasury_business = nonfinancialcorporatebusinesstre+nonfinancialnoncorporatebusiness

g bill_mutual_fund = mutualfundstreasurybillsasset
g bill_mmf = moneymarketfundstreasurybillsass
g bill_insurance = lifeinsurancecompaniestreasurybi
g bill_row = restoftheworldtreasurybillsandce
g bill_gov = -federalgovernmenttreasurybillsli
g bill_fed = monetaryauthoritytreasurybillsas

keep tq treasury_* bill_*
foreach var of varlist treasury_* bill_* {
replace `var'=`var'/1000/4 // change to billion per quarter
}
sum bill_* if tq==tq(2020q1)
keep tq treasury_* bill_*
save FRB_Z1_F210, replace

* Level
insheet using "$folder\data\FRB_Z1_L210_20201025.csv",  clear 
g tq = quarterly(series, "YQ")
format tq %tq
g treasury_mutual_fund = mutualfundstreasurysecuritiesass
g treasury_bank = uschartereddepositoryinstitution
g treasury_broker_dealer = securitybrokersanddealerstreasur
g treasury_etf = exchangetradedfundstreasurysecur
g treasury_close_end_fund = closedendfundstreasurysecurities
g treasury_insurance = propertycasualtyinsurancecompani+lifeinsurancecompaniestreasuryse
g treasury_mmf = moneymarketfundstreasurysecuriti
g treasury_household = householdsandnonprofitorganizati
g treasury_gov = - federalgovernmenttreasurysecurit // add negative sign for treasury
g treasury_fed = monetaryauthoritytotaltreasuryse //monetaryauthoritytreasurysecurit
g treasury_row =	restoftheworldtreasurysecurities
g treasury_pension_fund =	privatepensionfundstreasurysecur+federalgovernmentdefinedcontribu
g treasury_local_gov =	stateandlocalgovernmentstreasury
g treasury_foreign_bank = foreignbankingofficesintheustrea
g treasury_abs = issuersofassetbackedsecuritiestr
g treasury_credit_union = creditunionstreasurysecuritiesas 
g treasury_gse = governmentsponsoredenterprisestr 
g treasury_business = nonfinancialcorporatebusinesstre+nonfinancialnoncorporatebusiness


keep tq treasury_*
foreach var of varlist treasury_* {
replace `var'=`var'/1000 // change to billion per 
rename `var' level_`var'
}
save FRB_Z1_L210, replace


* cross-section (2020q1)
use FRB_Z1_F210, clear
replace tq=tq-1 // match to 2019Q4 level
merge 1:1 tq using FRB_Z1_L210 
replace tq=tq+1 // 

drop _m
keep if tq==tq(2020q1)

keep tq treasury_* level_treasury_*
reshape long treasury_ level_treasury_, i(tq) j(type) string

replace type = "Mutual funds" if type == "mutual_fund"
replace type = "Banks" if type == "bank"
replace type = "Broker dealers" if type == "broker_dealer"
replace type = "ETFs" if type == "etf"
replace type = "Closed-end funds" if type == "close_end_fund"
replace type = "Insurance companies" if type == "insurance"
replace type = "Pension funds" if type == "pension_fund"
replace type = "Money market funds" if type == "mmf"
replace type = "Households" if type == "household"
replace type = "U.S. Treasury" if type == "gov"
replace type = "Rest of the world" if type == "row"
replace type = "Federal Reserve" if type == "fed"
replace type = "Local governments" if type == "local_gov"
replace type = "Foreign banking offices in U.S." if type == "foreign_bank"
replace type = "ABS" if type == "abs"
replace type = "Credit unions" if type == "credit_union"
replace type = "GSEs" if type == "gse"
replace type = "Nonfinancial business" if type == "business"


label var level_treas "Level"
label var treas "Flow"

label var treas ""


sort treasury_
g group = _n
labmask group, values(type) 
label var group "`: var label type'" 

rename treas Flow
rename level_treas Level

estpost tabstat  Flow Level, by(group)  
esttab . using "$folder\output\FOF_all.tex", ///
replace cells("Flow(fmt(2)) Level(fmt(2))") nomtitle nonumber varlabels(`e(labels)') eqlabels("A" "") width(\hsize)

foreach var of varlist Flow Level {
sum `var' if type=="U.S. Treasury"
replace `var' = `var' + `r(mean)' if type=="Federal Reserve"
}

replace type="Federal Reserve net Treasury" if type=="Federal Reserve"
labmask group, values(type) 
label var group "`: var label type'" 

drop if type=="Federal Reserve"
drop if type=="U.S. Treasury"

estpost tabstat  Flow Level, by(group)  
esttab . using "$folder\output\FOF_Fed_Treasury_Combined.tex", ///
replace cells("Flow(fmt(2)) Level(fmt(2))") nomtitle nonumber varlabels(`e(labels)') eqlabels("A" "") width(\hsize)




graph hbar Flow, over(type, sort(1)) ytitle("Change in Treasuries (billion)")  ///
 graphregion(color(white)) plotregion(color(white)) bgcolor(none)
graph export  $folder\output\FOF_2020Q1_Fed_Treasury_Combined.pdf, replace 



drop if type=="Federal Reserve net Treasury"

graph hbar Flow, over(type, sort(1)) ytitle("Change in Treasuries (billion)")  ///
 graphregion(color(white)) plotregion(color(white)) bgcolor(none)
graph export  $folder\output\FOF_2020Q1.pdf, replace 








*******************************************************************************
* monthly aggregate outflows 
*******************************************************************************

import excel using "$folder\data\agg_series_morningstar.xlsx",  clear first sheet(assets)
g equity = USEquity + SectorEquity
g bond = TaxableBond + MunicipalBond
g balance = Allocation
g others = Alter + Commodities

foreach var of varlist equity bond balance others {
replace `var'=`var'

}
g A = USCategoryGroup+"-01"
g date = date(A,"YMD",.)
format date %td
save temp_asset, replace




import excel using "$folder\data\agg_series_morningstar.xlsx",  clear first sheet(flows)
g equity = USEquity + SectorEquity
g bond = TaxableBond + MunicipalBond
g balance = Allocation
g others = Alter + Commodities

foreach var of varlist equity bond balance others TaxableBond MunicipalBond {
rename `var' `var'_flows

}
g A = USCategoryGroup+"-01"
g date = date(A,"YMD",.)
format date %td

merge 1:1 date using temp_asset
drop _m 

g tm=mofd(date)
merge 1:1 tm using  "$folder\data\bank_deposits"

sort date


foreach var of varlist equity bond balance others TaxableBond MunicipalBond {
replace `var'=`var'/10^9
replace `var'_flows=`var'_flows/10^9
}


foreach var of varlist equity bond balance others  TaxableBond MunicipalBond {
g `var'_gr=`var'_flows/`var'[_n-1]*100
}



twoway (area equity_gr date, xlabel(,format(%tdCY))  color(maroon)) if date>=td(01jan2000) & date<=td(30apr2020), ///
  graphregion(color(white)) plotregion(color(white)) bgcolor(none) ///
	legend(label(1 "Equity") ) ///
	 ytitle("Fund flows (%AUM)")  title("") ///	
	ylabel(-6(2)2) xtitle("")
graph export  $folder\output\aggregate_growth_equity_2000_2020.pdf, replace



twoway (area bond_gr date, xlabel(,format(%tdCY))) if date>=td(01jan2000) & date<=td(30apr2020), ///
  graphregion(color(white)) plotregion(color(white)) bgcolor(none) ///
	legend(label(1 "Bond")  ) ///
	 ytitle("Fund flows (%AUM)")  title("") ///	
	ylabel(-6(2)2) xtitle("")
graph export  $folder\output\aggregate_growth_bond_2000_2020.pdf, replace



twoway (area equity_flow date, xlabel(,format(%tdCY))  color(maroon)) if date>=td(01jan2000) & date<=td(30apr2020), ///
  graphregion(color(white)) plotregion(color(white)) bgcolor(none) ///
	legend(label(1 "Equity") ) ///
	 ytitle("Fund flows (billion)")  title("") ///	
	xtitle("")  ylabel(-300(100)100)
graph export  $folder\output\aggregate_flow_equity_2000_2020.pdf, replace




twoway (area bond_flow date, xlabel(,format(%tdCY))  color(navy)) if date>=td(01jan2000) & date<=td(30apr2020), ///
  graphregion(color(white)) plotregion(color(white)) bgcolor(none) ///
	legend(label(1 "Equity") ) ///
	 ytitle("Fund flows (billion)")  title("") ///	
	xtitle("") ylabel(-300(100)100)
graph export  $folder\output\aggregate_flow_bond_2000_2020.pdf, replace





twoway  ///
 (area  bond date, xlabel(,format(%tdCY))) if date>=td(01jan1995) & date<=td(30apr2020), ///
  graphregion(color(white)) plotregion(color(white)) bgcolor(none) ///
	legend(off) ///
	 ytitle("Fund assets (billion)")  title("") ///	
	ylabel(, nogrid) xtitle("") 
graph export  $folder\output\aggregate_assets.pdf, replace


twoway  ///
 (area  bond date, xlabel(,format(%tdCY))) if date>=td(01jan1995) & date<=td(31dec2019), ///
  graphregion(color(white)) plotregion(color(white)) bgcolor(none) ///
	legend(off) ///
	 ytitle("Fund assets (billion)")  title("") ///	
	ylabel(, nogrid) xtitle("") 
graph export  $folder\output\aggregate_assets_1995_2019.pdf, replace






g fund_deposit_share = bond/deposits

twoway  ///
 (area  fund_deposit_share date, xlabel(,format(%tdCY))) if date>=td(01jan1995) & date<=td(30apr2020), ///
  graphregion(color(white)) plotregion(color(white)) bgcolor(none) ///
	legend(off) ///
	 ytitle("Fixed-income fund shares / Bank deposits")  title("") ///	
	ylabel(0.15(.05).35, nogrid) xtitle("") 
graph export  $folder\output\fund_deposit_share.pdf, replace


twoway  ///
 (area  fund_deposit_share date, xlabel(,format(%tdCY))) if date>=td(01jan1995) & date<=td(31dec2019), ///
  graphregion(color(white)) plotregion(color(white)) bgcolor(none) ///
	legend(off) ///
	 ytitle("Fixed-income fund shares / Bank deposits")  title("") ///	
	ylabel(0.15(.05).35, nogrid) xtitle("") 
graph export  $folder\output\fund_deposit_share_1995_2019.pdf, replace


